/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package primephysique;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author AP
 */
public class Statistic extends javax.swing.JPanel {

    /**
     * Creates new form Statistic
     */
    public Statistic() {
        initComponents();
        //add date for combobox
        try {
            cdb = new ConnectDB();
            con = cdb.getConnection();
            st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String query = "SELECT BranchName FROM Branch";
            rs = st.executeQuery(query);
            Vector brData = new Vector();

            brData.add("All");
            while (rs.next()) {
                brData.add(rs.getString(1));
            }
            cbbBranch.setModel(new DefaultComboBoxModel(brData));
            cbbBranchTeacher.setModel(new DefaultComboBoxModel(brData));

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private String convertDate(String date) {
        String rDate = "";
        try {
            SimpleDateFormat dfs = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            Date sT = null;
            sT = new Date(dfs.parse(date).getTime());
            SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");

            rDate = df.format(sT);
        } catch (Exception ex) {
            Logger.getLogger(Statistic.class.getName()).log(Level.SEVERE, null, ex);
        }

        return rDate;

    }

    private String getDayOfMonth(int month, int year) {
        String day = "";
        switch (month) {
            case 1:
            case 3:
            case 5:
            case 7:
            case 8:
            case 10:
            case 12: {
                day = "31";
                break;
            }
            case 4:
            case 6:
            case 9:
            case 11: {
                day = "30";
                break;
            }
            case 2:
                if ((year % 4) == 0) {
                    day = "29";
                } else {
                    day = "28";
                }
        }
        return day;
    }
    //1. Trainee

    private void showTrainee() {

        try {
            cdb = new ConnectDB();
            con = cdb.getConnection();
            st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            int m = cbbMonthTrainee.getSelectedIndex();
            int y = cbbYearTrainee.getSelectedIndex();
            String year = cbbYearTrainee.getSelectedItem().toString().trim();
            //get current time         
            java.util.Date cdate = new Date();
            Timestamp date = new java.sql.Timestamp(cdate.getTime());
            //   System.out.println(date);
            // get range of infomation
            String query = "";
            DateFormat df = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");
            String select = "";
            Date selectedDate = null;
            String active = "'true'";
            if (ckbLearning.isSelected()) {
                active = "'true'";
            } else {
                active = "'false'";
            }
            if (m == 0) {
                if (y == 0) {
                    query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Email,Height,Weight,ClassID "
                            + "FROM Trainee WHERE Active=" + active + " AND ClassID IN (SELECT ClassID FROM Class "
                            + "WHERE StartDate< '" + date + "')";
                    query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Email,Height,Weight,StartDate,EndDate,Trainee.ClassID "
                            + "FROM Trainee,Class WHERE Trainee.ClassID=Class.ClassID AND Active=" + active
                            + "AND StartDate<'" + date + "'";

                } else {
                    select = "01/01/" + year + " 00:00:00.0";
                    //   System.out.println(select);
                    selectedDate = df.parse(select);
                    date = new java.sql.Timestamp(selectedDate.getTime());
                    //   System.out.println(da);
                    query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Email,Height,Weight,ClassID "
                            + "FROM Trainee WHERE Active=" + active + " AND ClassID IN (SELECT ClassID FROM Class WHERE "
                            + "StartDate> '" + date + "' AND StartDate<'" + year + "-12-31 23:59:59.999')";
                    //       System.out.println(query);
                    query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Email,Height,Weight,StartDate,EndDate,Trainee.ClassID "
                            + "FROM Trainee,Class WHERE Trainee.ClassID=Class.ClassID AND Active= " + active
                            + "AND StartDate> '" + date + "' AND StartDate<'" + year + "-12-31 23:59:59.999'";

                }
            } else {
                if (y == 0) {
                    //if get all year
                    //if (odd % 2 == 0) {
                    JOptionPane.showMessageDialog(null, "Please choose year !");
                    cbbMonthTrainee.setSelectedIndex(0);
                    //  odd++;
                    //}
                    return;
                } else {
                    select = m + "/01/" + year + " 00:00:00";
                    selectedDate = df.parse(select);
                    query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Email,Height,Weight,ClassID FROM Trainee "
                            + "WHERE Active='true' AND ClassID IN (SELECT ClassID FROM Class WHERE "
                            + "StartDate> '" + year + "-" + m + "-01 00:00:00.0'"
                            + "AND StartDate<'" + year + "-" + m + "-" + getDayOfMonth(m, Integer.parseInt(year)) + " 23:59:59.999')";
                    query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Email,Height,Weight,StartDate,EndDate,Trainee.ClassID "
                            + "FROM Trainee,Class WHERE Trainee.ClassID=Class.ClassID AND Active= " + active
                            + " AND StartDate> '" + year + "-" + m + "-01 00:00:00.0'"
                            + "AND StartDate<'" + year + "-" + m + "-" + getDayOfMonth(m, Integer.parseInt(year)) + " 23:59:59.999')";

                }
            }

            //add to table
            rs = st.executeQuery(query);
            rsmd = rs.getMetaData();
            Vector<String> columnName = new Vector<String>();
            Vector<String> columnData;
            //get column name
            columnName.add("Index");
            for (int i = 0; i < rsmd.getColumnCount() - 1; i++) {
                columnName.add(rsmd.getColumnName(i + 1));
            }
            columnName.add("Class Name");
            // set table column's Name
            model = new DefaultTableModel();
            int cc = rsmd.getColumnCount() + 1;
            for (int i = 0; i < cc; i++) {
                model.addColumn(columnName.get(i));
            }
            int index = 1;
            while (rs.next()) {
                columnData = new Vector<String>();
                columnData.add("" + index);
                //classname revenue
                columnData.add(rs.getString("FullName"));
                columnData.add(rs.getString("DateOfBirth"));
                //get text Gender
                String gen = "";
                if (rs.getString("Gender").equals("1")) {
                    gen = "Male";
                } else {
                    gen = "Female";
                }
                columnData.add(gen);
                columnData.add(rs.getString("Address"));
                columnData.add(rs.getString("Phone"));
                columnData.add(rs.getString("Email"));
                columnData.add(rs.getString("Height"));
                columnData.add(rs.getString("Weight"));
                columnData.add(convertDate(rs.getString("StartDate")));
                columnData.add(convertDate(rs.getString("EndDate")));
                //add branch name
                query = "SELECT ClassName FROM Class WHERE ClassID ='" + rs.getString("ClassID") + "'";
                ResultSet brName = con.createStatement().executeQuery(query);

                brName.next();
                columnData.add(brName.getString("ClassName"));

                model.addRow(columnData);
                //         columnData.clear();
                index++;
            }

            tbResult.setModel(model);
            scpResult.setViewportView(tbResult);



            con.close();


        } catch (Exception ex) {

            ex.printStackTrace();
        }
    }
    //2. Staff

    private void showResultStaff() {
        try {
            cdb = new ConnectDB();
            con = cdb.getConnection();
            st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            String b = cbbBranch.getSelectedItem().toString().trim();
            //         
            String query = "";

            if (b.equalsIgnoreCase("all")) {
                query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Salary,BranchID FROM Staff WHERE Authority= '0'";

            } else {
                query = "SELECT FullName,DateOfBirth,Gender,Address,Phone,Salary,BranchID FROM Staff WHERE Authority= '0' "
                        + "AND BranchID IN (SELECT BranchID FROM Branch WHERE BranchName='" + b + "')";
                //       System.out.println(query);
            }


            //add to table
            rs = st.executeQuery(query);
            rsmd = rs.getMetaData();
            Vector<String> columnName = new Vector<String>();
            Vector<String> columnData;
            //get column name
            columnName.add("Index");
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                columnName.add(rsmd.getColumnName(i + 1));
            }
            // set table column's Name
            model = new DefaultTableModel();
            int cc = rsmd.getColumnCount() + 1;
            for (int i = 0; i < cc; i++) {
                model.addColumn(columnName.get(i));
            }
            int index = 1;
            while (rs.next()) {
                columnData = new Vector<String>();
                columnData.add("" + index);
                //classname revenue
                columnData.add(rs.getString("FullName"));
                columnData.add(convertDate(rs.getString("DateOfBirth")));
                //get text Gender
                String gen = "";
                if (rs.getString("Gender").equals("1")) {
                    gen = "Male";
                } else {
                    gen = "Female";
                }
                columnData.add(gen);
                columnData.add(rs.getString("Address"));
                columnData.add(rs.getString("Phone"));
                columnData.add(rs.getString("Salary"));
                //add branch name
                query = "SELECT BranchName FROM branch WHERE BranchID ='" + rs.getString("BranchID") + "'";
                ResultSet brName = con.createStatement().executeQuery(query);

                brName.next();
                columnData.add(brName.getString("BranchName"));

                model.addRow(columnData);
                //         columnData.clear();
                index++;
            }

            tbResultStaff.setModel(model);
            scpResultStaff.setViewportView(tbResultStaff);



            con.close();


        } catch (Exception ex) {

            ex.printStackTrace();
        }
    }
    //3. teacher

    private void showResultTeacher() {
        try {
            cdb = new ConnectDB();
            con = cdb.getConnection();
            st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            String b = cbbBranchTeacher.getSelectedItem().toString().trim();
            //         
            String query = "";

            if (b.equalsIgnoreCase("all")) {
                query = "SELECT TeacherName,Teacher.Address,Phone,Salary,Branch.BranchName  "
                        + "FROM Teacher,Class,Branch WHERE Teacher.TeacherID=Class.TeacherID "
                        + "AND Branch.BranchID=Class.BranchID";

            } else {
                query = "SELECT TeacherName,Teacher.Address,Phone,Salary,Branch.BranchName  "
                        + "FROM Teacher,Class,Branch WHERE Teacher.TeacherID=Class.TeacherID "
                        + "AND Branch.BranchID=Class.BranchID AND Branch.BranchName ='" + b + "'";
                //  System.out.println(query);
            }
            //add to table
            rs = st.executeQuery(query);
            rsmd = rs.getMetaData();
            Vector<String> columnName = new Vector<String>();
            Vector<String> columnData;
            //get column name
            columnName.add("Index");
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                columnName.add(rsmd.getColumnName(i + 1));
            }
            // set table column's Name
            model = new DefaultTableModel();
            int cc = rsmd.getColumnCount() + 1;
            for (int i = 0; i < cc; i++) {
                model.addColumn(columnName.get(i));
            }
            int index = 1;
            while (rs.next()) {
                columnData = new Vector<String>();
                columnData.add("" + index);
                //classname revenue
                columnData.add(rs.getString("TeacherName"));
                columnData.add(rs.getString("Address"));
                columnData.add(rs.getString("Phone"));
                columnData.add(rs.getString("Salary"));
                //add branch name
//                query = "SELECT BranchName FROM branch WHERE BranchID ='" + rs.getString("BranchID") + "'";
//                ResultSet brName = con.createStatement().executeQuery(query);
//
//                brName.next();
                columnData.add(rs.getString("BranchName"));

                model.addRow(columnData);
                //         columnData.clear();
                index++;
            }

            tbResultTeacher.setModel(model);
            scpResultTeacher.setViewportView(tbResultTeacher);



            con.close();


        } catch (Exception ex) {

            ex.printStackTrace();
        }
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        tbpStatistic = new javax.swing.JTabbedPane();
        pnlStaff = new javax.swing.JPanel();
        pnlStaffTop = new javax.swing.JPanel();
        btnSearch = new javax.swing.JButton();
        cbbBranch = new javax.swing.JComboBox();
        lbBranch = new javax.swing.JLabel();
        pnlStaffCenter = new javax.swing.JPanel();
        scpResultStaff = new javax.swing.JScrollPane();
        tbResultStaff = new javax.swing.JTable();
        pnlTrainee = new javax.swing.JPanel();
        pnlTop = new javax.swing.JPanel();
        lbYear = new javax.swing.JLabel();
        cbbYearTrainee = new javax.swing.JComboBox();
        lbMonth = new javax.swing.JLabel();
        cbbMonthTrainee = new javax.swing.JComboBox();
        lbLearning = new javax.swing.JLabel();
        ckbLearning = new javax.swing.JCheckBox();
        pnlCenter = new javax.swing.JPanel();
        scpResult = new javax.swing.JScrollPane();
        tbResult = new javax.swing.JTable();
        pnlTeacher = new javax.swing.JPanel();
        pnlTeacherTop = new javax.swing.JPanel();
        lbBranch1 = new javax.swing.JLabel();
        cbbBranchTeacher = new javax.swing.JComboBox();
        btnSearch1 = new javax.swing.JButton();
        pnlTeacherCenter = new javax.swing.JPanel();
        scpResultTeacher = new javax.swing.JScrollPane();
        tbResultTeacher = new javax.swing.JTable();

        setLayout(new java.awt.BorderLayout());

        tbpStatistic.setTabPlacement(javax.swing.JTabbedPane.LEFT);

        pnlStaff.setLayout(new java.awt.BorderLayout());

        btnSearch.setText("Search");
        btnSearch.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSearchActionPerformed(evt);
            }
        });

        cbbBranch.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "All", "Ha noi", "Ho chi minh", "Da nang" }));

        lbBranch.setText("Branch");

        javax.swing.GroupLayout pnlStaffTopLayout = new javax.swing.GroupLayout(pnlStaffTop);
        pnlStaffTop.setLayout(pnlStaffTopLayout);
        pnlStaffTopLayout.setHorizontalGroup(
            pnlStaffTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlStaffTopLayout.createSequentialGroup()
                .addGap(81, 81, 81)
                .addComponent(lbBranch)
                .addGap(28, 28, 28)
                .addComponent(cbbBranch, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btnSearch)
                .addContainerGap(169, Short.MAX_VALUE))
        );
        pnlStaffTopLayout.setVerticalGroup(
            pnlStaffTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlStaffTopLayout.createSequentialGroup()
                .addGap(30, 30, 30)
                .addGroup(pnlStaffTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.CENTER)
                    .addComponent(btnSearch)
                    .addComponent(lbBranch)
                    .addComponent(cbbBranch, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(47, Short.MAX_VALUE))
        );

        pnlStaff.add(pnlStaffTop, java.awt.BorderLayout.PAGE_START);

        tbResultStaff.setModel(model);
        scpResultStaff.setViewportView(tbResultStaff);

        javax.swing.GroupLayout pnlStaffCenterLayout = new javax.swing.GroupLayout(pnlStaffCenter);
        pnlStaffCenter.setLayout(pnlStaffCenterLayout);
        pnlStaffCenterLayout.setHorizontalGroup(
            pnlStaffCenterLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(scpResultStaff, javax.swing.GroupLayout.DEFAULT_SIZE, 461, Short.MAX_VALUE)
        );
        pnlStaffCenterLayout.setVerticalGroup(
            pnlStaffCenterLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(scpResultStaff, javax.swing.GroupLayout.DEFAULT_SIZE, 347, Short.MAX_VALUE)
        );

        pnlStaff.add(pnlStaffCenter, java.awt.BorderLayout.CENTER);

        tbpStatistic.addTab("Staff", new javax.swing.ImageIcon(getClass().getResource("/images/001_55.png")), pnlStaff); // NOI18N

        pnlTrainee.setLayout(new java.awt.BorderLayout());

        pnlTop.setPreferredSize(new java.awt.Dimension(355, 80));

        lbYear.setText("Year :");

        cbbYearTrainee.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "All", "2012", "2011", "2010" }));
        cbbYearTrainee.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                cbbYearTraineeItemStateChanged(evt);
            }
        });

        lbMonth.setText("Month :");

        cbbMonthTrainee.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "All", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }));
        cbbMonthTrainee.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                cbbMonthTraineeItemStateChanged(evt);
            }
        });

        lbLearning.setText("Learning :");

        ckbLearning.setSelected(true);
        ckbLearning.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                ckbLearningItemStateChanged(evt);
            }
        });

        javax.swing.GroupLayout pnlTopLayout = new javax.swing.GroupLayout(pnlTop);
        pnlTop.setLayout(pnlTopLayout);
        pnlTopLayout.setHorizontalGroup(
            pnlTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlTopLayout.createSequentialGroup()
                .addGap(28, 28, 28)
                .addComponent(lbMonth)
                .addGap(18, 18, 18)
                .addComponent(cbbMonthTrainee, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(35, 35, 35)
                .addComponent(lbYear)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(cbbYearTrainee, javax.swing.GroupLayout.PREFERRED_SIZE, 74, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(30, 30, 30)
                .addComponent(lbLearning)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(ckbLearning)
                .addContainerGap(48, Short.MAX_VALUE))
        );
        pnlTopLayout.setVerticalGroup(
            pnlTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlTopLayout.createSequentialGroup()
                .addGap(20, 20, 20)
                .addGroup(pnlTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(ckbLearning, javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(pnlTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(lbMonth)
                        .addComponent(cbbMonthTrainee, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(lbYear)
                        .addComponent(cbbYearTrainee, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(lbLearning)))
                .addGap(37, 37, 37))
        );

        pnlTrainee.add(pnlTop, java.awt.BorderLayout.PAGE_START);

        pnlCenter.setPreferredSize(new java.awt.Dimension(355, 221));

        tbResult.setModel(model);
        scpResult.setViewportView(tbResult);

        javax.swing.GroupLayout pnlCenterLayout = new javax.swing.GroupLayout(pnlCenter);
        pnlCenter.setLayout(pnlCenterLayout);
        pnlCenterLayout.setHorizontalGroup(
            pnlCenterLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(scpResult, javax.swing.GroupLayout.DEFAULT_SIZE, 461, Short.MAX_VALUE)
        );
        pnlCenterLayout.setVerticalGroup(
            pnlCenterLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(scpResult, javax.swing.GroupLayout.DEFAULT_SIZE, 367, Short.MAX_VALUE)
        );

        pnlTrainee.add(pnlCenter, java.awt.BorderLayout.CENTER);

        tbpStatistic.addTab("Trainee", new javax.swing.ImageIcon(getClass().getResource("/images/001_57.png")), pnlTrainee); // NOI18N

        pnlTeacher.setLayout(new java.awt.BorderLayout());

        lbBranch1.setText("Branch");

        cbbBranchTeacher.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "All", "Ha noi", "Ho chi minh", "Da nang" }));
        cbbBranchTeacher.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                cbbBranchTeacherItemStateChanged(evt);
            }
        });

        btnSearch1.setText("Search");
        btnSearch1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSearch1ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout pnlTeacherTopLayout = new javax.swing.GroupLayout(pnlTeacherTop);
        pnlTeacherTop.setLayout(pnlTeacherTopLayout);
        pnlTeacherTopLayout.setHorizontalGroup(
            pnlTeacherTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlTeacherTopLayout.createSequentialGroup()
                .addGap(56, 56, 56)
                .addComponent(lbBranch1)
                .addGap(28, 28, 28)
                .addComponent(cbbBranchTeacher, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(33, 33, 33)
                .addComponent(btnSearch1)
                .addContainerGap(167, Short.MAX_VALUE))
        );
        pnlTeacherTopLayout.setVerticalGroup(
            pnlTeacherTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(pnlTeacherTopLayout.createSequentialGroup()
                .addGap(18, 18, 18)
                .addGroup(pnlTeacherTopLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.CENTER)
                    .addComponent(lbBranch1)
                    .addComponent(cbbBranchTeacher, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(btnSearch1))
                .addContainerGap(59, Short.MAX_VALUE))
        );

        pnlTeacher.add(pnlTeacherTop, java.awt.BorderLayout.PAGE_START);

        tbResultTeacher.setModel(model);
        scpResultTeacher.setViewportView(tbResultTeacher);

        javax.swing.GroupLayout pnlTeacherCenterLayout = new javax.swing.GroupLayout(pnlTeacherCenter);
        pnlTeacherCenter.setLayout(pnlTeacherCenterLayout);
        pnlTeacherCenterLayout.setHorizontalGroup(
            pnlTeacherCenterLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(scpResultTeacher, javax.swing.GroupLayout.DEFAULT_SIZE, 461, Short.MAX_VALUE)
        );
        pnlTeacherCenterLayout.setVerticalGroup(
            pnlTeacherCenterLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(scpResultTeacher, javax.swing.GroupLayout.DEFAULT_SIZE, 347, Short.MAX_VALUE)
        );

        pnlTeacher.add(pnlTeacherCenter, java.awt.BorderLayout.CENTER);

        tbpStatistic.addTab("Teacher", new javax.swing.ImageIcon(getClass().getResource("/images/001_56.png")), pnlTeacher); // NOI18N

        add(tbpStatistic, java.awt.BorderLayout.CENTER);
    }// </editor-fold>//GEN-END:initComponents

    private void cbbYearTraineeItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_cbbYearTraineeItemStateChanged
        //       System.out.println("y=" + cbbYear.getSelectedItem().toString().trim());
        if (evt.getStateChange() == 1) {
            showTrainee();
        }
    }//GEN-LAST:event_cbbYearTraineeItemStateChanged

    private void cbbMonthTraineeItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_cbbMonthTraineeItemStateChanged
        //    System.out.println("m=" + cbbMonth.getSelectedIndex());
        if (evt.getStateChange() == 1) {
            showTrainee();
        }
    }//GEN-LAST:event_cbbMonthTraineeItemStateChanged

    private void btnSearchActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSearchActionPerformed
        // TODO add your handling code here:
        showResultStaff();
    }//GEN-LAST:event_btnSearchActionPerformed

    private void cbbBranchTeacherItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_cbbBranchTeacherItemStateChanged
    }//GEN-LAST:event_cbbBranchTeacherItemStateChanged

    private void btnSearch1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSearch1ActionPerformed
        showResultTeacher();
    }//GEN-LAST:event_btnSearch1ActionPerformed

    private void ckbLearningItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_ckbLearningItemStateChanged
        showTrainee();
    }//GEN-LAST:event_ckbLearningItemStateChanged
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnSearch;
    private javax.swing.JButton btnSearch1;
    private javax.swing.JComboBox cbbBranch;
    private javax.swing.JComboBox cbbBranchTeacher;
    private javax.swing.JComboBox cbbMonthTrainee;
    private javax.swing.JComboBox cbbYearTrainee;
    private javax.swing.JCheckBox ckbLearning;
    private javax.swing.JLabel lbBranch;
    private javax.swing.JLabel lbBranch1;
    private javax.swing.JLabel lbLearning;
    private javax.swing.JLabel lbMonth;
    private javax.swing.JLabel lbYear;
    private javax.swing.JPanel pnlCenter;
    private javax.swing.JPanel pnlStaff;
    private javax.swing.JPanel pnlStaffCenter;
    private javax.swing.JPanel pnlStaffTop;
    private javax.swing.JPanel pnlTeacher;
    private javax.swing.JPanel pnlTeacherCenter;
    private javax.swing.JPanel pnlTeacherTop;
    private javax.swing.JPanel pnlTop;
    private javax.swing.JPanel pnlTrainee;
    private javax.swing.JScrollPane scpResult;
    private javax.swing.JScrollPane scpResultStaff;
    private javax.swing.JScrollPane scpResultTeacher;
    private javax.swing.JTable tbResult;
    private javax.swing.JTable tbResultStaff;
    private javax.swing.JTable tbResultTeacher;
    private javax.swing.JTabbedPane tbpStatistic;
    // End of variables declaration//GEN-END:variables
    private ConnectDB cdb = null;
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private ResultSetMetaData rsmd;
    private DefaultTableModel model = new DefaultTableModel();
    private int odd = 0;
}
